package com.igroupes.ovineherd.util;


import org.apache.commons.collections.CollectionUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public enum SQLUtils {
    ;

    public static String strWarp(String str) {
        if (str == null) {
            return null;
        }
        return "'" + str + "'";
    }

    public static void transactionCommit(SupplierWithException<Connection> connectionSupplier, ConsumerWithException<Connection> connectionConsumer) {
        Connection con = null;
        try {
            con = connectionSupplier.get();
            con.setAutoCommit(false);
            connectionConsumer.accept(con);
            con.commit();
        } catch (Throwable e) {
            if (con != null) {
                try {
                    con.rollback();// 设定setAutoCommit(false)若没有在catch中进行Connection的rollBack操作，操作的表就会被锁住，造成数据库死锁。虽然在执行con.close()的时候会释放锁，但若应用服务器使用了数据库连接池，连接不会被断开，从而不会放锁
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            ExceptionUtils.rethrow(e);
        } finally {
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

    }

    public static void commitWithoutTransaction(SupplierWithException<Connection> connectionSupplier, ConsumerWithException<Connection> connectionConsumer) {
        Connection con = null;
        try {
            con = connectionSupplier.get();
            connectionConsumer.accept(con);
        } catch (Throwable e) {
            ExceptionUtils.rethrow(e);
        } finally {
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

    }


    public static List<Map<String, Object>> queryToList(SupplierWithException<Connection> connectionSupplier, String querySQL, List<Object> placeHolder) {
        List<Map<String, Object>> data = new ArrayList<>();
        SQLUtils.commitWithoutTransaction(connectionSupplier, conn -> {
            PreparedStatement preparedStatement = conn.prepareStatement(querySQL);
            if (CollectionUtils.isNotEmpty(placeHolder)) {
                for (int i = 1; i <= placeHolder.size(); i++) {
                    preparedStatement.setObject(i, placeHolder.get(i - 1));
                }
            }
            ResultSet resultSet = preparedStatement.executeQuery();
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCnt = metaData.getColumnCount();
            while (resultSet.next()) {
                Map<String, Object> map = new HashMap<>();
                for (int i = 1; i <= columnCnt; i++) {
                    map.put(metaData.getColumnName(i), resultSet.getObject(i));
                }
                data.add(map);
            }
        });
        return data;
    }


}
